<?php
    class Default_Model_Product extends Zend_Db_Table_Abstract
    {
        protected $_name = 'product';
        protected $_primary = 'product_id';
        protected $_db;
        
        public function __construct()
        {
            $this->_db = Zend_Registry::get('db');
        }
        
        public function listProduct($cat_id)
        {
            $product = array(
                'pd.product_id', 
                'pd.sort_name', 
                'pd.product_image', 
                'pd.original_price', 
                'pd.sales_price', 
            );
            $area = array(
                'a.area_name',
            );
            $provider = array(
                'pv.provider_name', 
                'pv.provider_icon',
            );           
            $sql = $this->_db->select()
                            ->from('product as pd', $product)
                            ->joinLeft('area as a', 'pd.area_id = a.area_id', $area)
                            ->joinLeft('provider as pv', 'pd.provider_id = pv.provider_id', $provider)
                            ->where('pd.status = 1')
                            ->where('TIMEDIFF(expiry_day, NOW()) > 0')
                            ->where('pd.cat_id = ?', $cat_id)
                            ->order('product_id DESC')
                            ->limit(3);
            $result = $this->_db->fetchAll($sql);
            return $result;
        }
        
        public function listProductByCategory($cat_id)
        {
            $product = array(
                'pd.product_id',
                'pd.sort_name', 
                'pd.product_image', 
                'pd.original_price', 
                'pd.sales_price',
            );
            $area = array(
                'a.area_name',
            );
            $provier = array(
                'pv.provider_name',
                'pv.provider_icon',
            );
            $sql = 'SELECT   , 
                             ,  
                    FROM     product AS pd, area AS a, provider AS pv
                    WHERE    pd.area_id = a.area_id
                    AND      pd.provider_id = pv.provider_id
                    AND      pd.status = 1
                    AND      pd.cat_id = ' . $cat_id . '
                    ORDER BY product_id DESC';
            $query = $this->_db->select()
                            ->from('product as pd', $product)
                            ->joinLeft('area as a', 'pd.area_id = a.area_id', $area)
                            ->joinLeft('provider as pv', 'pd.provider_id = pv.provider_id', $provier)
                            ->where('pd.status = 1')
                            ->where('pd.cat_id = ?', $cat_id)
                            ->where('TIMEDIFF(expiry_day, NOW()) > 0')
                            ->order('product_id DESC');
            return $query;
        }
        
        public function getProductById($product_id)
        {
            $sql = 'SELECT   pd.product_id, pd.sort_name, pd.product_name, pd.product_image, pd.original_price, pd.sales_price,
                             pd.product_advantages, pd.product_conditions, pd.product_content, pd.product_link,
                             pd.content_plaintext, a.area_name, pv.provider_name, pv.provider_icon, ca.cat_name,
                             YEAR(pd.expiry_day) as expiry_year, MONTH(pd.expiry_day) as expiry_month,
                             DAY(pd.expiry_day) as expiry_day, HOUR(pd.expiry_day) as expiry_hour, 
                             MINUTE(pd.expiry_day) as expiry_minute, SECOND(pd.expiry_day) as expiry_second
                    FROM     product AS pd, area AS a, provider AS pv, category as ca
                    WHERE    pd.area_id = a.area_id
                    AND      pd.provider_id = pv.provider_id
                    AND      pd.cat_id = ca.cat_id
                    AND      pd.status = 1
                    AND      pd.product_id = ' . $product_id;
            $result = $this->_db->fetchRow($sql);
            return $result;
        }
        
        public function listHotProducts()
        {
            $sql = $this->_db->select()
                            ->from($this->_name)
                            ->where('status = 1')
                            ->where('TIMEDIFF(expiry_day, NOW()) > 0')
                            ->where('hot = 1')
                            ->order('product_id DESC');
            $result = $this->_db->fetchAll($sql);
            return $result;
        }
        
        public function searchProduct($keyword, $cat_id, $area_id)
        {
            $where = "MATCH(product_name, advantages_plaintext, conditions_plaintext, content_plaintext) AGAINST('" . $keyword . "' IN NATURAL LANGUAGE MODE)";
            if($cat_id != '0')
            {
                $where .= ' AND cat.cat_id = ' . $cat_id;
            }
            if($area_id != '0')
            {
                $where .= ' AND a.area_id = ' . $area_id;
            }
            $product = array(
                'pd.product_id', 
                'pd.sort_name',
                'pd.product_image',
                'pd.status',
                'pd.expiry_day',
            );
            $area = array(
                'a.area_name',
            );
            $category = array(
                'cat.cat_name',
            );
            $sql = $this->_db->select()
                        ->from('product as pd', $product)
                        ->joinLeft('area as a', 'pd.area_id = a.area_id', $area)
                        ->joinLeft('category as cat', 'pd.cat_id = cat.cat_id', $category)
                        ->where($where)
                        ->where('TIMEDIFF(pd.expiry_day, NOW()) > 0')
                        ->where('pd.status = 1');
            return $sql;
        }
        
        public function percent($percent)
        {
            $percent = explode('.', $percent);
            return $percent[0];
        }
        
        public function formatMoney($number, $fractional=false) {
			if ($fractional) {
				$number = sprintf('%f', $number);
			}
			while (true) {
				$replaced = preg_replace('/(-?\d+)(\d\d\d)/', '$1.$2', $number);
				if ($replaced != $number) {
					$number = $replaced;
				} else {
					break;
				}
			}
			return $number;
		}
        
        public function spaceString($string)
        {
            while (strpos($string, "  ")) 
                {
                    $string = str_replace("  ", " ", $string);
                }
        }
        
        public function cut_string($str,$len,$more){
			if ($str=='' || $str==NULL) return $str;
			if (is_array($str)) return $str;
			$str = trim($str);
			if (strlen($str) <= $len) return $str;
			$str = substr($str,0,$len);
			if ($str != '') {
				if (!substr_count($str," ")) {
					if ($more) $str .= " ...";
					return $str;
				}
				while(strlen($str) && ($str[strlen($str)-1] != " ")) $str = substr($str,0,-1);
				$str = substr($str,0,-1);
				if ($more) $str .= " ...";
			}
			return $str;
		}
    }
